# -*- coding: utf-8 -*-
"""
Excel导出工具
"""

import sys
import logging
import pandas as pd
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Any

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent.parent
sys.path.insert(0, str(project_root))

from config.settings import DATA_CONFIG

logger = logging.getLogger(__name__)


class ExcelExporter:
    """Excel导出器"""
    
    def __init__(self, output_path: str = None):
        """
        初始化Excel导出器
        
        Args:
            output_path: 输出文件路径，默认使用配置文件中的路径
        """
        self.output_path = Path(output_path) if output_path else DATA_CONFIG['excel_output_path']
        
        # 确保输出目录存在
        self.output_path.parent.mkdir(parents=True, exist_ok=True)
    
    def export_leads_data(self, leads_data: List[Dict[str, Any]], sheet_name: str = "商业地产线索"):
        """
        导出线索数据到Excel
        
        Args:
            leads_data: 线索数据列表
            sheet_name: 工作表名称
        """
        try:
            if not leads_data:
                logger.warning("没有数据可导出")
                return
            
            # 转换为DataFrame
            df = pd.DataFrame(leads_data)
            
            # 重命名列为中文
            column_mapping = {
                'id': 'ID',
                'city': '城市',
                'contact_person': '联系人',
                'contact_info': '联系方式',
                'business_type': '业态',
                'acceptable_rent': '承受租金',
                'building_area': '建筑面积',
                'publish_date': '发布日期',
                'detail_url': '详情页URL',
                'crawl_time': '抓取时间',
                'data_source': '数据来源',
                'data_status': '数据状态',
                'created_at': '创建时间',
                'updated_at': '更新时间'
            }
            
            # 只保留存在的列
            existing_columns = {k: v for k, v in column_mapping.items() if k in df.columns}
            df = df.rename(columns=existing_columns)
            
            # 格式化日期列
            date_columns = ['发布日期', '抓取时间', '创建时间', '更新时间']
            for col in date_columns:
                if col in df.columns:
                    df[col] = pd.to_datetime(df[col], errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S')
            
            # 导出到Excel
            with pd.ExcelWriter(self.output_path, engine='openpyxl') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                
                # 获取工作表对象进行格式化
                worksheet = writer.sheets[sheet_name]
                
                # 自动调整列宽
                for column in worksheet.columns:
                    max_length = 0
                    column_letter = column[0].column_letter
                    
                    for cell in column:
                        try:
                            if len(str(cell.value)) > max_length:
                                max_length = len(str(cell.value))
                        except:
                            pass
                    
                    adjusted_width = min(max_length + 2, 50)  # 最大宽度50
                    worksheet.column_dimensions[column_letter].width = adjusted_width
            
            logger.info(f"数据已导出到Excel: {self.output_path}")
            logger.info(f"导出记录数: {len(df)}")
            
        except Exception as e:
            logger.error(f"Excel导出失败: {e}")
            raise
    
    def export_summary_report(self, summary_data: Dict[str, Any]):
        """
        导出汇总报告到Excel
        
        Args:
            summary_data: 汇总数据
        """
        try:
            # 创建多个工作表的Excel文件
            with pd.ExcelWriter(self.output_path, engine='openpyxl') as writer:
                
                # 总体统计
                if 'overall_stats' in summary_data:
                    stats_df = pd.DataFrame([summary_data['overall_stats']])
                    stats_df.to_excel(writer, sheet_name="总体统计", index=False)
                
                # 按城市统计
                if 'city_stats' in summary_data:
                    city_df = pd.DataFrame(summary_data['city_stats'])
                    city_df.to_excel(writer, sheet_name="城市统计", index=False)
                
                # 按业态统计
                if 'business_type_stats' in summary_data:
                    business_df = pd.DataFrame(summary_data['business_type_stats'])
                    business_df.to_excel(writer, sheet_name="业态统计", index=False)
                
                # 数据质量报告
                if 'quality_report' in summary_data:
                    quality_df = pd.DataFrame([summary_data['quality_report']])
                    quality_df.to_excel(writer, sheet_name="数据质量", index=False)
            
            logger.info(f"汇总报告已导出到Excel: {self.output_path}")
            
        except Exception as e:
            logger.error(f"汇总报告导出失败: {e}")
            raise
    
    def create_sample_data(self):
        """创建示例数据用于测试"""
        sample_data = [
            {
                'id': 1,
                'city': '深圳',
                'contact_person': '张先生',
                'contact_info': '138****8888',
                'business_type': '餐饮',
                'acceptable_rent': '5000-8000元/月',
                'building_area': '100-200平米',
                'publish_date': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'detail_url': 'https://example.com/detail/1',
                'crawl_time': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'data_source': 'pupuwang',
                'data_status': 'active'
            },
            {
                'id': 2,
                'city': '深圳',
                'contact_person': '李女士',
                'contact_info': '139****9999',
                'business_type': '餐饮',
                'acceptable_rent': '8000-12000元/月',
                'building_area': '200-300平米',
                'publish_date': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'detail_url': 'https://example.com/detail/2',
                'crawl_time': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'data_source': 'pupuwang',
                'data_status': 'active'
            }
        ]
        
        self.export_leads_data(sample_data, "示例数据")
        return sample_data


def export_database_to_excel():
    """从数据库导出所有数据到Excel"""
    from src.database.connection import db_manager
    from src.database.models import RealEstateLead
    
    try:
        with db_manager.get_session() as session:
            # 查询所有线索数据
            leads = session.query(RealEstateLead).all()
            
            # 转换为字典列表
            leads_data = []
            for lead in leads:
                lead_dict = {
                    'id': lead.id,
                    'city': lead.city,
                    'contact_person': lead.contact_person,
                    'contact_info': lead.contact_info,
                    'business_type': lead.business_type,
                    'acceptable_rent': lead.acceptable_rent,
                    'building_area': lead.building_area,
                    'publish_date': lead.publish_date,
                    'detail_url': lead.detail_url,
                    'crawl_time': lead.crawl_time,
                    'data_source': lead.data_source,
                    'data_status': lead.data_status,
                    'created_at': lead.created_at,
                    'updated_at': lead.updated_at
                }
                leads_data.append(lead_dict)
            
            # 导出到Excel
            exporter = ExcelExporter()
            if leads_data:
                exporter.export_leads_data(leads_data)
            else:
                logger.info("数据库中暂无线索数据，创建示例数据...")
                exporter.create_sample_data()
                
    except Exception as e:
        logger.error(f"数据库导出失败: {e}")
        raise


if __name__ == "__main__":
    # 设置日志
    logging.basicConfig(level=logging.INFO)
    
    # 导出数据库数据到Excel
    export_database_to_excel()
